<?php 
// No direct access
 
defined( '_JEXEC' ) or die( 'Restricted access' );
 
jimport( 'joomla.application.component.model' );
 
/**
 * Volunteer Model
 *
 * @package    GippslandSite
 * @subpackage Components
 */
class VolunteerModelVolunteer extends JModel
{
    /**
    * Gets list of all locations in db
    * @return array of locations(id, name)
    */
    function getLocations()
    {
	    $db =& JFactory::getDBO();
	    $query = 'SELECT id,location as name FROM gps_location';
	    $db->setQuery($query);
 		$rows = $db->loadObjectList();
 		return $rows;
    }
	
	/**
    * Gets list of all timspans in db
    * @return array of timspans(id, name)
    */
    function getTimespans()
    {
		$db =& JFactory::getDBO();
	    $query = 'SELECT id,timespan as name FROM gps_timespan';
	    $db->setQuery($query);
 		$rows = $db->loadObjectList();
 		return $rows;
    }
	
	/**
    * Gets list of all industries in db
    * @return array of industries(id, name)
    */
    function getIndustries()
    {
		$db =& JFactory::getDBO();
	    $query = 'SELECT id,industry as name FROM gps_industry';
	    $db->setQuery($query);
 		$rows = $db->loadObjectList();
 		return $rows;
    }
    	
    /**
    * Adds a new mp3 file details into the database
    *Return true if success
    */   
    
	function addFile($name, $description, $locationId, $timespanId, $industryId, $keywordList, $fileUrl, $acknowledment)
	{
		
		//test
		/*$name="BBB aaaaaa";
		$description="dwwwOOOOOOOOOOOOOOOOOaaaaacrip";
		$locationId=2;
		$timespanId=3;
		$industryId=3;
		$keywordList="French island,junGLE";
		$fileUrl="localhost/mp3/AAAA.mp3";
		$acknowledment="this is a aaaaaaaaacknow2";*/
		//test end
		
		
		$host="localhost";
		$user="root";
		$password="";
		$db="gippslanddb";
		$sqlconn= mysqli_connect($host,$user,$password,$db) or die("Can not connect with mysql server"); 

		
		//inserting mp3 files
		
		
		$sqlstr1= "INSERT INTO gps_audiofile (name,url,location_id,timespan_id,industry_id,description,acknowledgement) VALUES ('".$name."','".$fileUrl."',".$locationId.",".$timespanId.",".$industryId.",'".$description."','".$acknowledment."')";				
		$result1=mysqli_query($sqlconn,$sqlstr1) or die("Error querring");
		//
		//$sqlstr1= "INSERT INTO gps_audiofile (name,url,location_id,timespan_id,industry_id,description,acknowledgement) VALUES ('".$name."','".$fileUrl."',".$locationId.",".$timespanId.",".$industryId.",'".$description."','".$acknowledment."')";			
			
		//Get most recent file_id
		$sqlstr_various="SELECT MAX(id) FROM gps_audiofile WHERE name='".$name."'";
		$result2=mysqli_query($sqlconn,$sqlstr_various) or die("Error querring");
		$row=mysqli_fetch_array($result2,MYSQL_NUM);
			
		$file_id=$row[0];
		//file id - end
		
		//inserting file_id and keyword_id into gps_filekeywords table - start
		
		$keyword_arr=explode(",",$keywordList);
		
		if (sizeof($keyword_arr)>0)
		{
			foreach($keyword_arr as $item)
			{
				
				$sql_select="SELECT id FROM gps_keyword WHERE keyword='".$item."'";
				$result3=mysqli_query($sqlconn,$sql_select) or die("Error querring");
				$row=mysqli_fetch_array($result3,MYSQL_NUM);
				$keyword_id=$row[0];
				
				$sqlstr_various="INSERT INTO gps_filekeywords (file_id,keyword_id) VALUES (".$file_id.",".$keyword_id.")";
			
				$result4=mysqli_query($sqlconn,$sqlstr_various);// or die("Error querring");
			}	
		}
		//inserting file_id and keyword_id into gps_filekeywords table - end
		
		
		if($result1==1 && $result2==1 && $result3==1)// && $result4==1)
		{
			$x=true;
		}
		else
		{
			$x=false;	
		}
		
		
		return $x;
	}

	/**
	* Items total
	* @var integer
	*/
	var $_total = null;
 
	/**
	* Pagination object
	* @var object
	*/
	var $_pagination = null;
	
	function getPagination()
	{
		// Load the content if it doesn't already exist
        if (empty($this->_pagination)) {
            jimport('joomla.html.pagination');
            $this->_pagination = new JPagination($this->getTotal(), $this->getState('limitstart'), $this->getState('limit') );
        }
        return $this->_pagination;
	} 
	
	function getTotal()
	{
        // Load the content if it doesn't already exist
        if (empty($this->_total)) {
            $query = $this->_buildQuery();
            $this->_total = $this->_getListCount($query);    
        }
        return $this->_total;
	}
	
	function getData() 
	{
        // if data hasn't already been obtained, load it
        if (empty($this->_data)) {
            $query = $this->_buildQuery();
            $this->_data = $this->_getList($query, $this->getState('limitstart'), $this->getState('limit')); 
        }
		
        return $this->_data;
	}
	
	function _buildQuery()
	{
		return 'SELECT * FROM gps_audiofile';
	}
	
	function __construct()
	{
        parent::__construct();
 
        global $mainframe, $option;
 
        // Get pagination request variables
        $limit = 1;//$mainframe->getUserStateFromRequest('global.list.limit', 'limit', $mainframe->getCfg('list_limit'), 'int');
        $limitstart = JRequest::getVar('limitstart', 0, '', 'int');
 
        // In case limit has been changed, adjust it
        $limitstart = ($limit != 0 ? (floor($limitstart / $limit) * $limit) : 0);
 
        $this->setState('limit', $limit);
        $this->setState('limitstart', $limitstart);
	}
	
	
	////ghda

	function getSearchresults($location_id,$timespan_id,$industry_id,$opt_keywords)
    {
		 
		
		//test
		$location_id=2;
		$timespan_id=3;
		$industry_id=3;
		$opt_keywords = "jungle,french island";	
		
	
		
		/////////////////////
		
		$db =& JFactory::getDBO();
	    
		//creating sql string - start
		$sqlstr="SELECT * from gps_audiofile";
		$flag_and=0;
		
		if($location_id>0 or $timespan_id>0 or $industry_id>0)
		{
			$sqlstr .= " where 1=1";
			
			if($location_id>0)
			{
				$sqlstr .= ' && gps_audiofile.location_id='.$location_id;		
			}
			if($timespan_id>0)
			{
				$sqlstr .= ' && gps_audiofile.timespan_id='.$timespan_id;
			}	
			if($industry_id>0)
			{
				$sqlstr .= ' && gps_audiofile.industry_id='.$industry_id;	
			}
		
		}
		
		//ECHO $sqlstr;

		$opt_keywords=strip_tags($opt_keywords);
		$opt_keywords_arr=trim($opt_keywords_arr);
		
		
		$opt_keywords_arr = explode(',',$opt_keywords);
 		
 		/*
	    if(sizeof($opt_keywords_arr)>0)
	    foreach ($rows as $i)
	    {
		 	$file_id=$i->id;
		 	$query_str2=" LEFT JOIN gps_audiofile.id=gps_filekeywords.file_id 
		 	
		 	
		 	
		 	echo $x." and "; 
	    } 
	    */
	    $db->setQuery($sqlstr);
 		$rows = $db->loadObjectList();
	    
 		 foreach ($rows as $i)
	    {
		 	$file_id=$i->id;
		// 	$query_str2=" LEFT JOIN gps_audiofile.id=gps_filekeywords.file_id 
		 	
		 	
		 	
		 	echo $file_id." and "; 
	    } 
 		
 		
 		
	    //echo $rows[0];
	    
	    
	 //   return $rows;
		
		
		
		
		
		//////////////////////end ghda2
		

// 		
// 		
// 	//test
// 		print_r($audiofile_name); 
// 	//test - end 
// 		  
		
    }
	
	
	
	
	//ghda
	
	
}